Assign permissions to SQL Server objects

Assign permissions to SQL Server objects


I use the following queries to assign permissions to objects:

--Stored Procedure:
SELECT 'GRANT EXECUTE ON '+name+' TO [username]'
   FROM sys.objects
   WHERE TYPE='P' AND name LIKE 'usp_%'

--Table
SELECT 'GRANT SELECT ON '+name+' TO [username]'
FROM sys.objects
WHERE TYPE='U'

The following script can be used to create a login and user:
USE [master]
GO
CREATE LOGIN [username] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [Database-Name]
GO
CREATE USER [username] FOR LOGIN [username]
GO

Seyed Hamed Vahedi Seyed Hamed Vahedi     Sat, 19 January, 2019